import pandas as pd
import os
import matplotlib.pyplot as plt

file = r'Data\Derivational_EVUsage_Data.csv'
df = pd.read_csv(file)

# 把'User ID'导出到新对象df_user,并保证没有重复
df_user = df.drop_duplicates(subset=['User ID'])
# 只保留'User ID'一列
df_user = df_user[['User ID']]

# 统计每个'User ID'出现的次数
df_user['Buy_count'] = df.groupby('User ID')['User ID'].transform('count')

# 统计每个'User ID'中'Fee'的总和
df_user['Fee_sum'] = df.groupby('User ID')['Fee'].transform('sum')
# 统计每个'User ID'中'Fee'的平均值
df_user['Fee_mean'] = df.groupby('User ID')['Fee'].transform('mean')

# 统计每个'User ID'中'Fee'的最大值
df_user['Fee_max'] = df.groupby('User ID')['Fee'].transform('max')

# 统计每个'User ID'中最后一次'Start Date'的时间
df_user['Last_Start_Date'] = df.groupby('User ID')['Start Date'].transform('max')

# 统计每个'User ID'中'is_peak'的次数除以'Buy_count'
df_user['is_peak_count'] = df.groupby('User ID')['is_peak'].transform('sum')
df_user['is_peak_rate'] = df_user['is_peak_count'] / df_user['Buy_count']

# 统计每个'User ID'中'is_weekday'的次数除以'Buy_count'
df_user['is_weekday_count'] = df.groupby('User ID')['is_weekday'].transform('sum')
df_user['is_weekday_rate'] = df_user['is_weekday_count'] / df_user['Buy_count']

# 统计每个'User ID'中'Average Cost per kWh'的平均值
df_user['Average_Cost_per_kWh'] = df.groupby('User ID')['Average Cost per kWh'].transform('mean')

# 统计每个'User ID'中'Ended By'== 'Plug Out at Vehicle'的次数
df_user['Plug_Out_at_Vehicle_count'] = df.groupby('User ID')['Ended By'].transform(lambda x: (x == 'Plug Out at Vehicle').sum())

# 统计每个'User ID'中'Ended By'== 'Customer'的次数
df_user['Customer_count'] = df.groupby('User ID')['Ended By'].transform(lambda x: (x == 'Customer').sum())

# 统计每个'User ID'中'Season'== 'Winter'的次数
df_user['Winter_count'] = df.groupby('User ID')['Season'].transform(lambda x: (x == 'Winter').sum())

# 统计每个'User ID'中'Season'== 'Spring'的次数
df_user['Spring_count'] = df.groupby('User ID')['Season'].transform(lambda x: (x == 'Spring').sum())

# 统计每个'User ID'中'Season'== 'Summer'的次数
df_user['Summer_count'] = df.groupby('User ID')['Season'].transform(lambda x: (x == 'Summer').sum())

# 统计每个'User ID'中'Season'== 'Fall'的次数
df_user['Fall_count'] = df.groupby('User ID')['Season'].transform(lambda x: (x == 'Fall').sum())

# 删除缺失值的行
df_user = df_user.dropna()

# 删除重复的行
df_user = df_user.drop_duplicates()

df_user.to_csv(r'Data\Derivational_User_Data.csv', index=False)